package com.javasm.finance.dao.impl;

import com.javasm.finance.dao.MenuDao;
import com.javasm.finance.entity.Menu;
import com.javasm.finance.util.DBUtils;
import org.apache.commons.dbutils.BasicRowProcessor;
import org.apache.commons.dbutils.GenerousBeanProcessor;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.Connection;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class MenuDaoImpl implements MenuDao {
    QueryRunner runner=new QueryRunner();

    @Override
    public List<Menu> findMenu(Integer userId,Integer level) throws SQLException {
        Connection conn = DBUtils.getConn();
        String sql="select am.menu_id,am.menu_name,am.parent_id,am.menu_url,am.glyphicon,am.version_id " +
                "from fin_admin_menu am,fin_user_menu um " +
                "where am.menu_id=um.menu_id " +
                "and um.user_id=? ";
        if (level == 1) {
            //一级菜单数据
            sql += "and am.parent_id=0";
        } else {
            //二级菜单数据
            sql += "and am.parent_id!=0";
        }
        BeanListHandler<Menu> listHandler = new BeanListHandler<>(Menu.class, new BasicRowProcessor(new GenerousBeanProcessor()));
        List<Menu> menuList = runner.query(conn, sql, listHandler, userId);
        DBUtils.getClose(conn,null,null);
        return menuList;
    }

    @Override
    public List<Menu> findAllMenu(Integer page, Integer pageSize,String menuName,Integer parentId) throws SQLException {
        Connection conn = DBUtils.getConn();
        String sql = "select m1.menu_id,m1.menu_name,m1.parent_id,ifnull(m2.menu_name,'超级管理') parent_name,m1.menu_url,m1.glyphicon " +
                "from " +
                "fin_admin_menu m1 " +
                "left join " +
                "fin_admin_menu m2 " +
                "on " +
                "m1.parent_id=m2.menu_id ";
        //条件查询
        boolean isWhere=true;
        List paramList=new ArrayList();
        if (menuName!=null&&!"".equals(menuName)){
            if (isWhere){
                sql+="where ";
                isWhere=false;
            }
            sql += "m1.menu_name like ? ";
            paramList.add("%" + menuName + "%");
        }
        if (parentId!=null&&!"".equals(parentId)){
            if (isWhere){
                sql+="where ";
            }else {
                sql+="and ";
            }
            sql+="m1.parent_id=? ";
            paramList.add(parentId);
        }
        //分页查询
        sql += "limit " + (page - 1) * pageSize + "," + pageSize;
        BeanListHandler<Menu> listHandler = new BeanListHandler<>(Menu.class, new BasicRowProcessor(new GenerousBeanProcessor()));
        List<Menu> menuList = runner.query(conn, sql, listHandler,paramList.toArray());
        DBUtils.getClose(conn,null,null);
        return menuList;
    }

    @Override
    public long findTotalNum(String menuName,Integer parentId) throws SQLException {
        Connection conn = DBUtils.getConn();
        String sql = "select count(1) totalNum from fin_admin_menu m1 ";
        //条件查询
        boolean isWhere=true;
        List paramList=new ArrayList();
        if (menuName!=null&&!"".equals(menuName)){
            if (isWhere){
                sql+="where ";
                isWhere=false;
            }
            sql += "m1.menu_name like ? ";
            paramList.add("%" + menuName + "%");
        }
        if (parentId!=null&&!"".equals(parentId)){
            if (isWhere){
                sql+="where ";
            }else {
                sql+="and ";
            }
            sql+="m1.parent_id=? ";
            paramList.add(parentId);
        }
        long totalNum = runner.query(conn, sql, new ScalarHandler<>(),paramList.toArray());
        DBUtils.getClose(conn,null,null);
        return totalNum;
    }

    @Override
    public List<Menu> findAllOneMenu() throws SQLException {
        Connection conn = DBUtils.getConn();
        String sql = "select am.menu_id,am.menu_name,am.parent_id,am.menu_url,am.glyphicon,am.version_id " +
                "from fin_admin_menu am " +
                "where am.parent_id=0";
        BeanListHandler<Menu> listHandler = new BeanListHandler<>(Menu.class, new BasicRowProcessor(new GenerousBeanProcessor()));
        List<Menu> oneMenuList = runner.query(conn, sql, listHandler);
        DBUtils.getClose(conn, null, null);
        return oneMenuList;
    }

    @Override
    public int addMenu(Menu menu) throws SQLException {
        Connection conn = DBUtils.getConn();
        String sql = "insert into fin_admin_menu(menu_id,menu_name,parent_id,menu_url,glyphicon,version_id) values (?,?,?,?,?,?)";
        int rows = runner.update(conn, sql, menu.getMenuId(), menu.getMenuName(), menu.getParentId(), menu.getMenuUrl(), menu.getGlyphicon(), menu.getVersionId());
        DBUtils.getClose(conn, null, null);
        return rows;
    }

    @Override
    public Menu queryByMenuId(Integer menuId) throws SQLException {
        Connection conn = DBUtils.getConn();
        String sql = "select menu_id,menu_name,parent_id,menu_url,glyphicon,version_id from fin_admin_menu where menu_id=?";
        BeanHandler<Menu> beanHandler = new BeanHandler<>(Menu.class, new BasicRowProcessor(new GenerousBeanProcessor()));
        Menu menu = runner.query(conn, sql, beanHandler, menuId);
        DBUtils.getClose(conn, null, null);
        return menu;
    }

    @Override
    public int editMenu(Menu menu) throws SQLException {
        Connection conn = DBUtils.getConn();
        String sql = "update fin_admin_menu " +
                "set menu_name=?,parent_id=?,menu_url=?,glyphicon=?,version_id=? " +
                "where menu_id=?";
        int rows = runner.update(conn, sql, menu.getMenuName(), menu.getParentId(), menu.getMenuUrl(),
                menu.getGlyphicon(), menu.getVersionId() + 1, menu.getMenuId());
        DBUtils.getClose(conn, null, null);
        return rows;
    }

    @Override
    public int deleteMenu(Integer menuId, Connection conn) throws SQLException {
        String sql = "delete from fin_admin_menu where menu_id=?";
        int rows = runner.update(conn, sql, menuId);
        return rows;
    }

    @Override
    public List<Menu> querySecondMenu(Integer parentId) throws SQLException {
        Connection conn = DBUtils.getConn();
        String sql = "select menu_id,menu_name,parent_id,menu_url,glyphicon,version_id from fin_admin_menu where parent_id=?";
        BeanListHandler<Menu> beanListHandler = new BeanListHandler<>(Menu.class, new BasicRowProcessor(new GenerousBeanProcessor()));
        List<Menu> menuList = runner.query(conn, sql, beanListHandler, parentId);
        DBUtils.getClose(conn, null, null);
        return menuList;
    }

    @Override
    public int deleteByParentId(Integer parentId, Connection conn) throws SQLException {
        String sql = "delete from fin_admin_menu where parent_id=?";
        int rows = runner.update(conn, sql, parentId);
        return rows;
    }
}
